﻿Imports System.Data.OleDb

Imports System

Public Class frmAdminUser

    Public TomoFoto As String = "No"

    Dim commandEmpleado As OleDbCommand
    Dim commandUsuario As OleDbCommand

    Private Sub frmAdminUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        txtCedula.Focus()

        bd.openConnection()
        bd.queryConsultaEmpleado("")
        bd.closeConnection()

        dgvUser.RowHeadersVisible = False
        dgvUser.Columns(2).HeaderText = "Primer Apellido"
        dgvUser.Columns(3).HeaderText = "Segundo Apellido"
        dgvUser.Columns(4).HeaderText = "Estado Civil"
        dgvUser.Columns(6).HeaderText = "Perfil"

        dgvUser.Columns(7).Visible = False
        dgvUser.Columns(8).Visible = False
        dgvUser.Columns(9).Visible = False
        dgvUser.Columns(10).Visible = False
        dgvUser.Columns(11).Visible = False
        dgvUser.Columns(12).Visible = False
        dgvUser.Columns(13).Visible = False
        dgvUser.Columns(14).Visible = False
        dgvUser.Columns(15).Visible = False
        dgvUser.Columns(16).Visible = False
        dgvUser.Columns(17).Visible = False
        dgvUser.Columns(18).Visible = False
        dgvUser.Columns(19).Visible = False
        dgvUser.Columns(20).Visible = False
        dgvUser.Columns(21).Visible = False

        dgvUser.ClearSelection()

        bd.queryComboBoxPerfil()

        btnActualizar.Enabled = False
        btnEliminar.Enabled = False

        dgvUser.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

        dgvUser.Columns(3).Width = 113

    End Sub

    Private Sub dgvUser_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvUser.CellClick

        If IsDBNull(dgvUser.Item(0, dgvUser.CurrentRow.Index).Value) Then
            txtCedula.Text = ""
        Else
            txtCedula.Text = dgvUser.Item(0, dgvUser.CurrentRow.Index).Value
        End If

        If IsDBNull(dgvUser.Item(1, dgvUser.CurrentRow.Index).Value) Then
            txtNombre.Text = ""
        Else
            txtNombre.Text = dgvUser.Item(1, dgvUser.CurrentRow.Index).Value
        End If

        If IsDBNull(dgvUser.Item(2, dgvUser.CurrentRow.Index).Value) Then
            txtApe1.Text = ""
        Else
            txtApe1.Text = dgvUser.Item(2, dgvUser.CurrentRow.Index).Value
        End If

        If IsDBNull(dgvUser.Item(3, dgvUser.CurrentRow.Index).Value) Then
            txtApe2.Text = ""
        Else
            txtApe2.Text = dgvUser.Item(3, dgvUser.CurrentRow.Index).Value
        End If

        Select Case dgvUser.Item(4, dgvUser.CurrentRow.Index).Value
            Case "Soltero"
                rdbSoltero.Checked = True
            Case "Casado"
                rdbCasado.Checked = True
            Case "Divorciado"
                rdbDivorciado.Checked = True
            Case Else
                rdbOtro.Checked = True
        End Select

        If IsDBNull(dgvUser.Item(7, dgvUser.CurrentRow.Index).Value) Then
            txtDireccion.Text = ""
        Else
            txtDireccion.Text = dgvUser.Item(7, dgvUser.CurrentRow.Index).Value
        End If

        dtpDate.Text = dgvUser.Item(8, dgvUser.CurrentRow.Index).Value

        txtUser.Text = dgvUser.Item(9, dgvUser.CurrentRow.Index).Value
        txtPassword.Text = dgvUser.Item(10, dgvUser.CurrentRow.Index).Value

        If (dgvUser.Item(12, dgvUser.CurrentRow.Index).Value = 0) Then
            rdbInactivo.Checked = True
        Else
            rdbActivo.Checked = True
        End If


        Dim fs As System.IO.FileStream = New System.IO.FileStream(Application.StartupPath & "\Data\photo\" & dgvUser.Item(13, dgvUser.CurrentRow.Index).Value, System.IO.FileMode.Open)
        picBox.Image = Image.FromStream(fs)
        fs.Close()

        txtCedula.Enabled = False
        txtNombre.Focus()
        btnAgregar.Text = "Nuevo"
        btnAgregar.Image = Image.FromFile(Application.StartupPath & "/Data/system/addUser.png")

        btnActualizar.Enabled = True
        btnEliminar.Enabled = True

        grpEstatus.Visible = True

        TomoFoto = "No"

        If txtUser.Text = "admin" Then
            txtUser.Enabled = False
            rdbActivo.Enabled = False
            chkbxCompras.Enabled = False
            chkbxCategorias.Enabled = False
            chkbxReportes.Enabled = False
            chkbxProveedor.Enabled = False
            chkbxFacturacion.Enabled = False
            chkbxUsuarios.Enabled = False
            chkbxInventario.Enabled = False
            chkbxRespaldos.Enabled = False
        Else
            txtUser.Enabled = True
            rdbActivo.Enabled = True
            chkbxCompras.Enabled = True
            chkbxCategorias.Enabled = True
            chkbxReportes.Enabled = True
            chkbxProveedor.Enabled = True
            chkbxFacturacion.Enabled = True
            chkbxUsuarios.Enabled = True
            chkbxInventario.Enabled = True
            chkbxRespaldos.Enabled = True

            chkbxCompras.Checked = dgvUser.Item(15, dgvUser.CurrentRow.Index).Value
            chkbxCategorias.Checked = dgvUser.Item(18, dgvUser.CurrentRow.Index).Value
            chkbxReportes.Checked = dgvUser.Item(16, dgvUser.CurrentRow.Index).Value
            chkbxProveedor.Checked = dgvUser.Item(17, dgvUser.CurrentRow.Index).Value
            chkbxFacturacion.Checked = dgvUser.Item(14, dgvUser.CurrentRow.Index).Value
            chkbxUsuarios.Checked = dgvUser.Item(20, dgvUser.CurrentRow.Index).Value
            chkbxInventario.Checked = dgvUser.Item(19, dgvUser.CurrentRow.Index).Value
            chkbxRespaldos.Checked = dgvUser.Item(21, dgvUser.CurrentRow.Index).Value
        End If
   


    End Sub

    Private Sub btnSlir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSalir.Click
        Me.Close()
    End Sub

    Private Sub txtSearchUser_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearchUser.TextChanged
        limpiaCampos()

        bd.openConnection()
        bd.queryConsultaEmpleado(txtSearchUser.Text)
        bd.closeConnection()

        dgvUser.ClearSelection()

        If txtSearchUser.Text.Trim = "" Then

            limpiaCampos()

            btnActualizar.Enabled = False
            btnEliminar.Enabled = False

        End If

    End Sub

    Private Sub btnAgregar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAgregar.Click

        If btnAgregar.Text = "Nuevo" Then

            txtCedula.Enabled = True

            btnActualizar.Enabled = False
            btnEliminar.Enabled = False

            limpiaCampos()

            txtCedula.Focus()

            btnAgregar.Text = "Guardar"
            btnAgregar.Image = Image.FromFile(Application.StartupPath & "/Data/system/saveUser.png")

            grpEstatus.Visible = False

        ElseIf btnAgregar.Text = "Guardar" Then

            If (txtCedula.Text.Trim = "") Then
                ErrorProvider1.Dispose()
                ErrorProvider1.SetError(txtCedula, "Este Campo es Requerido")
                TabControl1.SelectTab(0)
                txtCedula.Focus()
            ElseIf (txtNombre.Text.Trim = "") Then
                ErrorProvider1.Dispose()
                ErrorProvider1.SetError(txtNombre, "Este Campo es Requerido")
                TabControl1.SelectTab(0)
                txtNombre.Focus()
            ElseIf (txtApe1.Text.Trim = "") Then
                ErrorProvider1.Dispose()
                ErrorProvider1.SetError(txtApe1, "Este Campo es Requerido")
                TabControl1.SelectTab(0)
                txtApe1.Focus()
            ElseIf (txtUser.Text.Trim = "") Then
                ErrorProvider1.Dispose()
                ErrorProvider1.SetError(txtUser, "Este Campo es Requerido")
                TabControl1.SelectTab(1)
                txtUser.Focus()
            ElseIf txtPassword.Text.Trim = "" Then
                ErrorProvider1.Dispose()
                ErrorProvider1.SetError(txtPassword, "Este Campo es Requerido")
                TabControl1.SelectTab(1)
                txtPassword.Focus()
            Else

                ErrorProvider1.Dispose()

                Dim sqlEmpleado As String = "INSERT INTO empleado (Cedula, Nombre, Ape1, Ape2, Fecha_Nacimiento, Direccion, Estado_Civil, Estatus, ID_Perfil, Foto) " +
                          "values (@CEDULA, @NOMBRE, @APELLIDO1, @APELLIDO2, @NACIMIENTO, @DIRECCION, @E_CIVIL, @ESTATUS, @PERFIL, @FOTO)"

                commandEmpleado = New OleDbCommand(sqlEmpleado, bd.cn)

                commandEmpleado.Parameters.Add(New OleDbParameter("@CEDULA", OleDbType.VarWChar, 20))
                commandEmpleado.Parameters("@CEDULA").Value = txtCedula.Text
                commandEmpleado.Parameters.Add(New OleDbParameter("@NOMBRE", OleDbType.VarWChar, 20))
                commandEmpleado.Parameters("@NOMBRE").Value = txtNombre.Text
                commandEmpleado.Parameters.Add(New OleDbParameter("@APELLIDO1", OleDbType.VarWChar, 20))
                commandEmpleado.Parameters("@APELLIDO1").Value = txtApe1.Text
                commandEmpleado.Parameters.Add(New OleDbParameter("@APELLIDO2", OleDbType.VarWChar, 20))
                commandEmpleado.Parameters("@APELLIDO2").Value = txtApe2.Text
                commandEmpleado.Parameters.Add(New OleDbParameter("@NACIMIENTO", OleDbType.Date))
                commandEmpleado.Parameters("@NACIMIENTO").Value = dtpDate.Text
                commandEmpleado.Parameters.Add(New OleDbParameter("@DIRECCION", OleDbType.VarWChar, 100))
                commandEmpleado.Parameters("@DIRECCION").Value = txtDireccion.Text
                commandEmpleado.Parameters.Add(New OleDbParameter("@E_CIVIL", OleDbType.VarWChar, 20))
                For Each x As RadioButton In grpEstadoCivil.Controls
                    If x.Checked = True Then
                        commandEmpleado.Parameters("@E_CIVIL").Value = x.Text
                    End If
                Next

                commandEmpleado.Parameters.Add(New OleDbParameter("@PERFIL", OleDbType.VarWChar, 20))
                commandEmpleado.Parameters("@PERFIL").Value = cmbPerfil.SelectedValue
                '****************************REVISAR**************************************
                commandEmpleado.Parameters.Add(New OleDbParameter("@ESTATUS", OleDbType.VarWChar, 20))
                commandEmpleado.Parameters("@ESTATUS").Value = "1"
                commandEmpleado.Parameters.Add(New OleDbParameter("@FOTO", OleDbType.VarWChar, 20))
                If TomoFoto = "Si" Then
                    commandEmpleado.Parameters("@FOTO").Value = txtCedula.Text & ".jpg"
                Else
                    commandEmpleado.Parameters("@FOTO").Value = "userDefault.png"
                End If


                'mando sql a base de datos
                commandEmpleado.Connection.Open()
                commandEmpleado.ExecuteNonQuery()
                commandEmpleado.Connection.Close()

                Dim sqlUsuario As String = "INSERT INTO usuario (Cedula, Usuario, Clave) " +
                          "values (@CEDULA, @USUARIO, @CLAVE)"

                commandUsuario = New OleDbCommand(sqlUsuario, bd.cn)

                commandUsuario.Parameters.Add(New OleDbParameter("@CEDULA", OleDbType.VarWChar, 20))
                commandUsuario.Parameters("@CEDULA").Value = txtCedula.Text
                commandUsuario.Parameters.Add(New OleDbParameter("@USUARIO", OleDbType.VarWChar, 20))
                commandUsuario.Parameters("@USUARIO").Value = txtUser.Text
                commandUsuario.Parameters.Add(New OleDbParameter("@CLAVE", OleDbType.VarWChar, 20))
                commandUsuario.Parameters("@CLAVE").Value = txtPassword.Text

                'mando sql a base de datos
                commandUsuario.Connection.Open()
                commandUsuario.ExecuteNonQuery()
                commandUsuario.Connection.Close()

                'Refresca Grid
                bd.openConnection()
                bd.queryConsultaEmpleado("")
                bd.closeConnection()

                limpiaCampos()

                TabControl1.SelectTab(0)

                MsgBox("El Registro Fue Ingresado", MsgBoxStyle.Information)

            End If

        End If

    End Sub

    Private Sub ckbMostrarPass_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ckbMostrarPass.CheckedChanged
        If (ckbMostrarPass.Checked = True) Then
            txtPassword.UseSystemPasswordChar = False
        Else
            txtPassword.UseSystemPasswordChar = True
        End If

    End Sub

    Sub limpiaCampos()
        dgvUser.ClearSelection()

        txtCedula.Enabled = True
        txtCedula.Clear()
        txtNombre.Clear()
        txtApe1.Clear()
        txtApe2.Clear()
        rdbSoltero.Checked = True
        txtDireccion.Clear()
        dtpDate.Text = Now

        txtUser.Clear()
        txtPassword.Clear()
        rdbActivo.Checked = True

        chkbxCategorias.Checked = False
        chkbxCompras.Checked = False
        chkbxFacturacion.Checked = False
        chkbxInventario.Checked = False
        chkbxProveedor.Checked = False
        chkbxReportes.Checked = False
        chkbxRespaldos.Checked = False
        chkbxUsuarios.Checked = False



        Dim fs As System.IO.FileStream = New System.IO.FileStream(Application.StartupPath & "/Data/photo/userDefault.png", System.IO.FileMode.Open)
        picBox.Image = Image.FromStream(fs)
        fs.Close()
    End Sub

    Private Sub btnEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEliminar.Click


        Dim sqlUsuario As String = "DELETE FROM usuario WHERE Cedula = @CEDULA"
        commandUsuario = New OleDbCommand(sqlUsuario, bd.cn)
        commandUsuario.Parameters.Add(New OleDbParameter("@CEDULA", OleDbType.VarWChar, 20))
        commandUsuario.Parameters("@CEDULA").Value = txtCedula.Text

        'mando sql a base de datos
        commandUsuario.Connection.Open()
        commandUsuario.ExecuteNonQuery()
        commandUsuario.Connection.Close()

        Dim sqlEmpleado As String = "DELETE FROM empleado WHERE Cedula = @CEDULA"
        commandEmpleado = New OleDbCommand(sqlEmpleado, bd.cn)
        commandEmpleado.Parameters.Add(New OleDbParameter("@CEDULA", OleDbType.VarWChar, 20))
        commandEmpleado.Parameters("@CEDULA").Value = txtCedula.Text

        'mando sql a base de datos
        commandEmpleado.Connection.Open()
        Dim result = commandEmpleado.ExecuteNonQuery()
        commandEmpleado.Connection.Close()

        'Refresca Grid
        bd.openConnection()
        bd.queryConsultaEmpleado("")
        bd.closeConnection()

        If (result = 1) Then
            MsgBox("El Registro Fue Eliminado", MsgBoxStyle.Information)
            Try
                If (dgvUser.Item(13, dgvUser.CurrentRow.Index).Value) <> "userDefault.png" Then

                    Dim archivo As String = Application.StartupPath & "/Data/photo/" & txtCedula.Text & ".jpg"

                    My.Computer.FileSystem.DeleteFile(archivo)

                End If
            Catch ex As Exception
                MsgBox(ex.Message.ToString, MsgBoxStyle.Critical)
            End Try
        End If

        limpiaCampos()

        btnActualizar.Enabled = False
        btnEliminar.Enabled = False
        txtCedula.Focus()

        btnAgregar.Text = "Guardar"
        btnAgregar.Image = Image.FromFile(Application.StartupPath & "/Data/system/saveUser.png")

        grpEstatus.Visible = False

        TabControl1.SelectTab(0)

    End Sub

    Private Sub btnActualizar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActualizar.Click
        Dim EC As String = "Soltero"
        Dim nombreFoto As String

        For Each x As RadioButton In grpEstadoCivil.Controls
            If x.Checked = True Then
                EC = x.Text
            End If
        Next

        Dim est As String

        If (rdbActivo.Checked = True) Then
            est = 1
        Else
            est = 0
        End If

        If TomoFoto = "Si" Then
            nombreFoto = txtCedula.Text & ".jpg"
        Else
            If (dgvUser.Item(13, dgvUser.CurrentRow.Index).Value) <> "userDefault.png" Then
                nombreFoto = dgvUser.Item(13, dgvUser.CurrentRow.Index).Value
            Else
                nombreFoto = "userDefault.png"
            End If
        End If

        Dim sqlEmpelado As String = "UPDATE empleado SET Nombre = '" & txtNombre.Text & "', Ape1 = '" & txtApe1.Text & "', Ape2 = '" & txtApe2.Text & "', Estado_Civil = '" & EC & "', Fecha_Nacimiento = '" & CDate(dtpDate.Text).Date & "', Estatus = '" & est & "', ID_Perfil = " & cmbPerfil.SelectedValue & ", Foto = '" & nombreFoto & "' WHERE Cedula = @CEDULA"
        commandUsuario = New OleDbCommand(sqlEmpelado, bd.cn)
        commandUsuario.Parameters.Add(New OleDbParameter("@CEDULA", OleDbType.VarWChar, 20))
        commandUsuario.Parameters("@CEDULA").Value = txtCedula.Text
        commandUsuario.Connection.Open()
        Dim result1 = commandUsuario.ExecuteNonQuery()
        commandUsuario.Connection.Close()




        Dim sqlUsuario As String = "UPDATE usuario SET Usuario = '" & txtUser.Text & "', Clave = '" & txtPassword.Text & "', ModFacturacion = " _
                                        & chkbxFacturacion.Checked & ", ModCompras = " & chkbxCompras.Checked & ", ModReportes = " _
                                        & chkbxReportes.Checked & ", ModProveedor = " & chkbxProveedor.Checked & ", ModCatProducto = " _
                                        & chkbxCategorias.Checked & ", Modinventario = " & chkbxInventario.Checked & ", ModUsuarios = " _
                                        & chkbxUsuarios.Checked & ", ModRespaldo = " & chkbxRespaldos.Checked & " WHERE Cedula = @CEDULA"

        commandUsuario = New OleDbCommand(sqlUsuario, bd.cn)
        commandUsuario.Parameters.Add(New OleDbParameter("@CEDULA", OleDbType.VarWChar, 20))
        commandUsuario.Parameters("@CEDULA").Value = txtCedula.Text
        commandUsuario.Connection.Open()
        Dim result2 = commandUsuario.ExecuteNonQuery()
        commandUsuario.Connection.Close()

        'Refresca Grid
        bd.openConnection()
        bd.queryConsultaEmpleado("")
        bd.closeConnection()



        If result1 = 1 Or result2 = 1 Then

            If TomoFoto = "Si" Then
                If (dgvUser.Item(13, dgvUser.CurrentRow.Index).Value) <> "userDefault.png" Then

                    Dim archivo As String = Application.StartupPath & "/Data/photo/" & txtCedula.Text & ".jpg"

                    'My.Computer.FileSystem.RenameFile(archivo, "borrar.jpg")

                    My.Computer.FileSystem.DeleteFile(archivo)

                End If
                picBox.Image.Save(Application.StartupPath & "/Data/photo/" & txtCedula.Text & ".jpg")
            End If

            limpiaCampos()

            MsgBox("El Registro Fue Actualizado", MsgBoxStyle.Information)

            btnActualizar.Enabled = False
            btnEliminar.Enabled = False
            txtCedula.Focus()

            btnAgregar.Text = "Guardar"
            btnAgregar.Image = Image.FromFile(Application.StartupPath & "/Data/system/saveUser.png")

            grpEstatus.Visible = False

            TabControl1.SelectTab(0)

        End If

    End Sub

    Private Sub dgvUser_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvUser.CellContentClick

    End Sub
End Class